package com.bahiaapk.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import com.bahiaapk.dto.TwitterMessage;
import com.bahiaapk.util.BahiaUtil;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class TwitterMessageDAO {

	private Context context;
	private SQLiteDatabase database;
	private DBHelper dbHelper;

	public TwitterMessageDAO(Context context) {
		this.context = context;
	}

	public TwitterMessageDAO openWritable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}
	public TwitterMessageDAO openReadable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getReadableDatabase();
		return this;
	}
	

	public void close() {
		dbHelper.close();
	}
	
	public List<TwitterMessage> findAll () {
		this.openReadable();

		List<TwitterMessage> result = new ArrayList<TwitterMessage>();		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("select ID, name,  message, image, created_at  from tbl_twitter_messages order by ID desc limit 0,60", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(BahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	

	
	private TwitterMessage bindSQLite(Cursor cursor){
		TwitterMessage twitterMessage  = new TwitterMessage();
		SimpleDateFormat dateFormat =  new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);
		for (int i = 0; i < cursor.getColumnCount(); i++){
			try {	
				if("ID".equals(cursor.getColumnName(i)))
					twitterMessage.setId(cursor.getLong(i));
				else if("message".equals(cursor.getColumnName(i))) 
					twitterMessage.setMessage(cursor.getString(i));
				else if("image".equals(cursor.getColumnName(i))) 
					twitterMessage.setUrlImage(cursor.getString(i));
				else if("created_at".equals(cursor.getColumnName(i)))
					twitterMessage.setCreatedAt(dateFormat.parse(cursor.getString(i)));
				else if("name".equals(cursor.getColumnName(i)))
					twitterMessage.setName(cursor.getString(i));
			} catch (ParseException e) {
				Log.e(BahiaUtil.TAG, e.getMessage());
			}	
		}
		return twitterMessage;
	}

	public void insert(JSONArray jsonArray, String name) throws JSONException {
		this.openWritable();

		Cursor cursor = null;
		try{
			
			// TODO Auto-generated method stub
			cursor = database.rawQuery("DELETE FROM tbl_twitter_messages WHERE name = '"+name+"'", null);
			cursor.moveToFirst();
			cursor.close();
			database.beginTransaction();
			
			looptransation:
			for (int i = 0; i < jsonArray.length(); i++) {
				
				if(i>19){
					break looptransation;
				}
					
				
				JSONObject jsoObject = jsonArray.getJSONObject(i);
			    
				JSONObject jsoObjectPerfil = jsoObject.getJSONObject("user");
				
				try{
					if(jsoObject.getJSONObject("retweeted_status")!=null){
						jsoObjectPerfil = jsoObject.getJSONObject("retweeted_status");
						jsoObjectPerfil = jsoObjectPerfil.getJSONObject("user");
					}
				}catch (Exception e) {
					
				}
				
				boolean find = false;
		 		
				cursor = database.rawQuery("SELECT * " +
										   "FROM tbl_twitter_messages " +
										   "WHERE ID = "+jsoObject.getLong("id"), null);
				cursor.moveToFirst();
				
				if (!cursor.isAfterLast()){
					find =true;
				}
				cursor.close();

				
				if(!find){
					cursor = database.rawQuery(" INSERT INTO tbl_twitter_messages (" +
							"ID,"+
							"image, " +
							"message, " +
							"created_at," +
							"name )" +
							"	VALUES ( "+ 
							"" + jsoObject.getLong("id")  +", " +
							"'" + jsoObjectPerfil.getString("profile_image_url")  +"', " +
							"'" + jsoObject.getString("text").replace("'", " ")  +"', " +
							"'" + jsoObject.getString("created_at")  +"', " +
							"'" +	jsoObjectPerfil.getString("screen_name") 	+"');", null);
					cursor.moveToFirst();
					cursor.close();	
				}
			}
		
		}catch (SQLException e) {
			Log.e(BahiaUtil.TAG, e.toString());
		}finally {
			
			database.setTransactionSuccessful();
			database.endTransaction();
			
		}
		this.close();
	}

	public TwitterMessage findByPrimaryKey(TwitterMessage twitterMessageDetail) {
		this.openReadable();

		TwitterMessage result = null;		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT ID, message, image, name, created_at  FROM tbl_twitter_messages WHERE ID = '"+twitterMessageDetail.getId()+"'", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast())
				result = this.bindSQLite(cursor);
			
			cursor.close();
		}catch (SQLException e){
			Log.e(BahiaUtil.TAG, e.toString());
		}
		
		this.close();
		return result;
	}
}

